﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace RantopadService.Business
{
    public class KeySettingBusiness
    {
       
        public static DataTable getAllKeySettings(string wherelike,string type)
        {
            string mysql = "SELECT a.*,b.KEY_SETTING_TYPE_NAME from key_setting a,key_setting_type  b where NAME like '%{0}%'  and a.TYPE_ID=b.ID  and b.ID like '%{1}%'";
            mysql = string.Format(mysql, wherelike,type);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }

        public static DataTable getAllKeySettingsByID(string ID)
        {
            //string mysql = " select b.KEY_ID,b.DETAIL,c.KEY_SETTING_TYPE_NAME as KEY_SETTING_TYPE, a.NAME as KEY_SETTING_NAME,a.CONTENT as CONTENT FROM rantopad.key_setting a,key_setting_of_option b,key_setting_type c where  b.KEY_SETTING_ID=a.ID and c.ID=a.TYPE_ID  and b.OPTION_ID={0}";
            string mysql = " select a.* FROM key_setting_of_option a where   a.OPTION_ID={0}";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static DataTable getAllKeySettingsNameByID(string ID)
        {
            string mysql = " select b.KEY_ID,b.DETAIL,c.KEY_SETTING_TYPE_NAME as KEY_SETTING_TYPE, a.NAME as KEY_SETTING_NAME,a.CONTENT as CONTENT FROM rantopad.key_setting a,key_setting_of_option b,key_setting_type c where  b.KEY_SETTING_ID=a.ID and c.ID=a.TYPE_ID  and b.OPTION_ID={0}";
            //string mysql = " select a.* FROM key_setting_of_option a where   a.OPTION_ID={0}";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        //SELECT MACRO_NAME,CONTENT FROM rantopad.macro where user_name='liang'  and MACRO_NAME='大招1' LIMIT 1
        public static DataTable getMACRO(string userID)
        {
            string mysql = " SELECT * FROM rantopad.macro where user_name='{0}'   ";
            mysql = string.Format(mysql, userID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static DataTable getMACRO(string userID,string name)
        {
            string mysql = " SELECT * FROM rantopad.macro where user_name='{0}'    ";
            mysql = string.Format(mysql, userID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static string getKeySettingsTypeID(string name)
        {
            string mysql = "SELECT * from key_setting_type   where KEY_SETTING_TYPE_NAME like '%{0}%' ";
            mysql = string.Format(mysql, name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return ""; }
            var id = dr["ID"].ToString();
            dr.Close();
            myconnection.Close();
            return id;
        }
        public static string getKeyNameByID(string devtype,string id)
        {
            string mysql = "SELECT * from rantopad.key   where key_id='{0}' and device_type='{1}'  ";
            mysql = string.Format(mysql, id,devtype);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return ""; }
            var keyname = dr["KEY_NAME"].ToString();
            myconnection.Close();
            return keyname;
        }

        public static DataTable getAllKeySettingsType(string wherelike)
        {
            string mysql = "SELECT * from key_setting_type  where KEY_SETTING_TYPE_NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        
        public static int getAllKeySettingsCount(string wherelike)
        {
            string mysql = "SELECT count(ID) as count from key_setting where NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int getAllKeySettingsTypeCount(string wherelike)
        {
            string mysql = "SELECT count(ID) as count from key_setting_type where KEY_SETTING_TYPE_NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int checkData(string colNM, string key_settingID)
        {
            string mysql = "SELECT count(ID) as count from key_setting where {0} = '{1}' ";
            mysql = string.Format(mysql, colNM, key_settingID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }

        public static int checkKeySettingName(string key_settingName)
        {
            var count = checkData("NAME", key_settingName);
            return count;
        }
        public static int delKeySettingByID(string id)
        {
            string mysql = "delete from key_setting where ID='{0}'";
            mysql = string.Format(mysql, id);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            
            var count=mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }
        public static int delMacro(string userName ,string name)
        {
            string mysql = "delete from macro where user_name='{0}' ";
            mysql = string.Format(mysql, userName);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }
        public static int delKeySettingTypeByID(string id)
        {
            string mysql = "delete from key_setting_type where ID='{0}'";
            mysql = string.Format(mysql, id);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }


        public static int addKeySetting(string name, string content, string typeID)
        {
            //INSERT INTO `rantopad`.`key_setting` (`KEY_SETTING_TYPE_NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;
            try
            {
                string mysql = "insert into `key_setting` (`NAME`, `CONTENT`, `TYPE_ID`) VALUES ('{0}', '{1}', '{2}') ";
                mysql = string.Format(mysql,  name,  content,  typeID);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {
                count = 0;
            }
            

            return count;
        }
        public static int addMacro(string username, string content,  string hash)
        {
            //INSERT INTO `rantopad`.`key_setting` (`KEY_SETTING_TYPE_NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "insert into `macro` (`USER_NAME`, `CONTENT`, `TIME`, `HASH`) VALUES ('{0}',  '{1}',  '{2}',  '{3}') ";
                mysql = string.Format(mysql, username, content, DateTime.Now.ToString(), hash);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        public static int addKeySettingType(string name)
        {
            //INSERT INTO `rantopad`.`key_setting` (`KEY_SETTING_TYPE_NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "insert into `key_setting_type` (`KEY_SETTING_TYPE_NAME`) VALUES ('{0}') ";
                mysql = string.Format(mysql, name);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }

        internal static int updateKeySetting(string typeID, string name, string content,string id)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `key_setting` set `NAME`='{2}', `CONTENT`='{1}' ,TYPE_ID='{0}'  where `ID`='{3}'";
                mysql = string.Format(mysql,typeID,content, name, id);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        internal static int updateMacro(string userName,  string content,DateTime time,string hash)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `macro`  set     `CONTENT`='{1}', TIME='{2}', HASH='{3}'  where `user_name`='{0}' ";
                mysql = string.Format(mysql, userName,   content,time.ToString(),hash);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        internal static int updateKeySettingType(string typeName, string id)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `key_setting_type` set `KEY_SETTING_TYPE_NAME`='{1}'  where `ID`='{0}'";
                mysql = string.Format(mysql, id, typeName);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
    }
}